<?php


namespace App\Models;


use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

/**
 * Class DealerStat
 * @package App\Models
 */
class DealerStat extends Model
{
    /**
     * 表名
     *
     * @var string
     */
    protected $table = 'xpel_dealer_roll_stat';

    /**
     * 获取数据
     *
     * @param string $start_date
     * @param string $end_date
     * @return array|int[]
     */
    public function getTotal(string $start_date, string $end_date)
    {
        $sql = 'SELECT SUM(num) AS total, product_type_id FROM xpel_dealer_roll_stat  WHERE create_month>="%s" AND create_month<="%s" AND dealer_id=1 GROUP BY product_type_id';
        $sql = sprintf($sql, $start_date, $end_date);

        $data = DB::select($sql);
        $saleQmTotal = 0;
        $saleWinTotal = 0;

        if (empty($data)) {
            return [
                'qm_total' => $saleQmTotal,
                'win_total' => $saleWinTotal,
            ];
        }

        foreach ($data as $item) {
            if ($item->product_type_id == 1) {
                $saleQmTotal += $item->total;
            }
            if ($item->product_type_id == 2) {
                $saleWinTotal += $item->total;
            }
        }

        return [
            'qm_total' => $saleQmTotal,
            'win_total' => $saleWinTotal,
        ];
    }

    /**
     * 获取最佳销售数据
     *
     * @param $start_date
     * @param $end_date
     * @param int $userType
     * @return int[]
     */
    public function getSalesData(string $start_date, string $end_date, int $userType): array
    {
        if ($userType === 1) {
            return $this->getTotal($start_date, $end_date);
        }

        if ($userType === 3) {
            $sql = 'SELECT SUM(num) AS total, product_type_id, dealer_id FROM xpel_dealer_roll_stat  WHERE create_month>="%s" AND create_month<="%s" AND dealer_id!=dist_id AND dealer_id!=1 AND dist_id!=1 GROUP BY product_type_id, dealer_id ORDER BY total DESC';
        } else {
            $sql = 'SELECT SUM(num) AS total, product_type_id, dist_id FROM xpel_dealer_roll_stat LEFT JOIN users ON xpel_dealer_roll_stat.dealer_id=users.id WHERE users.user_type=2 AND create_month>="%s" AND create_month<="%s" GROUP BY product_type_id, dealer_id ORDER BY total DESC';
        }
        $sql = sprintf($sql, $start_date, $end_date);
        $data = DB::select($sql);

        $qmTotal = 0;
        $winTotal = 0;

        if (!empty($data)) {
            foreach ($data as $item) {
                if ($item->product_type_id == 1) {
                    $qmTotal = $qmTotal < $item->total ? $item->total : $qmTotal;
                }
                if ($item->product_type_id == 2) {
                    $winTotal = $winTotal < $item->total ? $item->total : $winTotal;
                }
            }
        }

        return [
            'qm_total' => $qmTotal,
            'win_total' => $winTotal
        ];
    }

    /**
     * 获取分数
     *
     * @param string $start_date
     * @param string $end_date
     * @param int $type
     * @param int $userType
     * @return int|mixed
     */
    public function getScore(string $start_date, string $end_date, int $type, int $userType)
    {
        $select = $type == 1 ? 'SUM(qm_score) AS total_score' : 'SUM(win_score) AS total_score';
        if ($userType === 2) {
            $sql = 'SELECT ' . $select . ',dist_id FROM xpel_dealer_stat left join users on users.id=xpel_dealer_stat.dealer_id WHERE users.user_type=2 AND create_month>="%s" AND create_month<="%s" AND dealer_id=dist_id GROUP BY dealer_id ORDER BY total_score DESC LIMIT 1';
        } elseif ($userType === 3) {
            $sql = 'SELECT ' . $select . ',dist_id FROM xpel_dealer_stat  WHERE create_month>="%s" AND create_month<="%s" AND dealer_id != dist_id AND dealer_id!=1 AND dist_id!=1 GROUP BY dealer_id ORDER BY total_score DESC LIMIT 1';
        } else {
            $sql = 'SELECT ' . $select . ',dist_id FROM xpel_dealer_stat  WHERE create_month>="%s" AND create_month<="%s" AND dealer_id=1';
        }
        $sql = sprintf($sql, $start_date, $end_date);
        $data = DB::select($sql);

        if (!empty($data) && !empty($data[0])) {
            return $data[0]->total_score;
        }

        return 0;
    }

    public function getAllSales(string $start_date, string $end_date, int $type)
    {
        $sql = 'SELECT SUM(num) AS total, film_name FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND product_type_id=%d AND dealer_id=1 GROUP BY film_type_id ORDER BY total DESC';
        $sql = sprintf($sql, $start_date, $end_date, $type);
        $result = DB::select($sql);

        if (empty($result)) {
            return [];
        }

        return $result;
    }

    public function getAllScores(string $start_date, string $end_date, int $type)
    {
        $select = $type === 1 ? 'qm_score' : 'win_score';
        $sql = 'SELECT SUM(' . $select . ') AS total, create_month as date FROM xpel_dealer_stat WHERE create_month>="%s" AND create_month<="%s" AND dealer_id=1 GROUP BY create_month ORDER BY create_month ASC ';
        $sql = sprintf($sql, $start_date, $end_date, $type);
        $result = DB::select($sql);

        if (empty($result)) {
            return [];
        }

        return $result;
    }

    public function getDistSales(string $start_date, string $end_date, int $type)
    {
        $sql = 'SELECT SUM(num) AS total, product_type_id, dist_id FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND product_type_id=%d AND dist_id=dealer_id GROUP BY dist_id ORDER BY total DESC LIMIT 1';
        $sql = sprintf($sql, $start_date, $end_date, $type);
        $data = DB::select($sql);
        if (empty($data)) {
            return [];
        }
        $dist_id = $data[0]->dist_id;
        $user = Admin::where('id', $dist_id)->first();
        $sql = 'SELECT SUM(num) AS total, film_name FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND product_type_id=%d AND dist_id=dealer_id AND dist_id="%d" GROUP BY film_type_id ORDER BY total DESC';
        $sql = sprintf($sql, $start_date, $end_date, $type, $dist_id);
        $result = DB::select($sql);

        if (empty($result)) {
            return [];
        }

        return [
            'list'    => $result,
            'abbr'      => $user->abbr,
        ];
    }

    public function getDistScores(string $start_date, string $end_date, int $type)
    {
        $select = $type === 1 ? 'qm_score' : 'win_score';
        $sql = 'SELECT SUM(' . $select . ') AS total_score, dealer_id FROM xpel_dealer_stat LEFT JOIN users ON xpel_dealer_stat.dealer_id=users.id WHERE users.user_type=2 AND create_month>="%s" AND create_month<="%s" GROUP BY dealer_id ORDER BY total_score DESC LIMIT 1';
        $sql = sprintf($sql, $start_date, $end_date);
        $data = DB::select($sql);
        if (empty($data)) {
            return [];
        }

        $dist_id = $data[0]->dealer_id;
        $user = Admin::where('id', $dist_id)->first();
        $sql = 'SELECT SUM(' . $select . ') AS total, create_month FROM xpel_dealer_stat LEFT JOIN users ON xpel_dealer_stat.dealer_id=users.id WHERE create_month>="%s" AND create_month<="%s" AND dealer_id="%d" GROUP BY create_month ORDER BY create_month ASC';
        $sql = sprintf($sql, $start_date, $end_date, $dist_id);
        $result = DB::select($sql);
        if (empty($result)) {
            return [];
        }

        return [
            'list'    => $result,
            'abbr'      => $user->abbr,
        ];
    }

    public function getDealerSales(string $start_date, string $end_date, int $type)
    {
        $sql = 'SELECT SUM(num) AS total, product_type_id, dealer_id FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND dealer_id!=dist_id AND dist_id!=1 AND dealer_id!=1 AND product_type_id=%d GROUP BY dealer_id ORDER BY total DESC LIMIT 1';
        $sql = sprintf($sql, $start_date, $end_date, $type);
        $data = DB::select($sql);
        if (empty($data)) {
            return [];
        }
        $dealer_id = $data[0]->dealer_id;
        $user = Admin::where('id', $dealer_id)->first();
        $sql = 'SELECT SUM(num) AS total, film_name FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND product_type_id=%d AND dealer_id="%d"  GROUP BY film_type_id ORDER BY total DESC';
        $sql = sprintf($sql, $start_date, $end_date, $type, $dealer_id);
        $result = DB::select($sql);
        if (empty($result)) {
            return [];
        }

        return [
            'list'    => $result,
            'abbr'      => $user->abbr,
        ];
    }

    public function getDealerScores(string $start_date, string $end_date, int $type)
    {
        $select = $type === 1 ? 'qm_score' : 'win_score';
        $sql = 'SELECT SUM(' . $select . ') AS total_score, dealer_id FROM xpel_dealer_stat  WHERE create_month>="%s" AND create_month<="%s" AND dist_id!=dealer_id AND dealer_id!=1 AND dist_id!=1 GROUP BY dealer_id ORDER BY total_score DESC LIMIT 1';
        $sql = sprintf($sql, $start_date, $end_date);
        $data = DB::select($sql);
        if (empty($data)) {
            return [];
        }

        $dealer_id = $data[0]->dealer_id;
        $user = Admin::where('id', $dealer_id)->first();
        $sql = 'SELECT SUM(' . $select . ') AS total, create_month as date FROM xpel_dealer_stat WHERE create_month>="%s" AND create_month<="%s"  AND dealer_id=%d GROUP BY create_month ORDER BY create_month ASC ';
        $sql = sprintf($sql, $start_date, $end_date, $dealer_id);
        $result = DB::select($sql);
        if (empty($result)) {
            return [];
        }

        return [
            'list'    => $result,
            'abbr'      => $user->abbr,
        ];
    }

}